IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientProcedureDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientProcedureDetail]
GO

CREATE TABLE [dbo].[PatientProcedureDetail](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CRNumber] [bigint] NOT NULL,
	[Procedure] [bigint] NOT NULL,
	[ProcedureDate] [datetime] NOT NULL,
	[Remarks] [varchar](50) NULL,
	[Consultant] [bigint] NOT NULL,
	[ReceiptId] [varchar](50) NULL,
	[Paid] [bit] NOT NULL
) ON [PRIMARY]
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientProcedureDetail TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientConsultantVisit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientConsultantVisit]
GO

CREATE TABLE [dbo].[PatientConsultantVisit](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CRNumber] [bigint] NOT NULL,
	[Consultant] [bigint] NOT NULL,
	[Date] [datetime] NOT NULL,
	[Paid] [bit] NOT NULL
) ON [PRIMARY]
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientConsultantVisit TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PatientWard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[PatientWard]
GO

CREATE TABLE [dbo].[PatientWard](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[CRNumber] [bigint] NOT NULL,
	[WardId] [bigint] NOT NULL,
	[BedId] [bigint] NOT NULL,
	[StartDate] [datetime] NOT NULL,
	[EndDate] [datetime] NULL
) ON [PRIMARY]

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.PatientWard TO hms_usr
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatient') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatient
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/02/2008
'SP Name    : dbo.SP_SetPatient
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Modified By         Date				Remarks                             
'-----------       ------------        ----------------------------------
'Swati				10/21/2008          Insert into PatientConsultantVisit
'------------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_SetPatient]

@vchPID    bigint,
@vchFName    Varchar(25),
@vchMName    Varchar(25),
@vchLName    Varchar(25),
@vchFatherFName    Varchar(25),
@vchFatherMName    Varchar(25),
@vchFatherLName    Varchar(25),
@vchMobilePhone    Varchar(25),
@vchHomePhone    Varchar(25),
@vchOfficePhone    Varchar(25),
@vchAddress1    Varchar(50),
@vchAddress2    Varchar(50),
@vchCity    Varchar(25),
@vchState    Varchar(25),
@vchZipCode    Varchar(20),
@vchDOB    Varchar(50),
@vchSex    Varchar(1),
@vchConsultant Varchar(50),
@vchPatientType Varchar(1),
@vchRegistrationFees smallmoney,
@vchConsultationFees smallmoney,
@vchInitialDeposit money,
@vchPackage Varchar(50),
@vchOutPID bigint OUTPUT,
@vchOutCRN bigint OUTPUT
                    
AS
DECLARE  @iRetValue integer, 
	     @biConsultantID bigint,
		 @biPackageID bigint,
	     @bConsultationFeesPaid bit
BEGIN TRANSACTION
   IF NOT EXISTS(SELECT     PID
   FROM Patient
   WHERE   (PID = @vchPID)
   )

   BEGIN
   INSERT INTO Patient(
       FName,
	   MName,
       LName,
	   FatherFName,
	   FatherMName,
	   FatherLName,
       Address1,
       Address2,
       City,
       State,
       ZipCode,
	   MobilePhone,
       HomePhone,
       OfficePhone,
       DOB,
	   Sex
  )
   VALUES (
       @vchFName,
	   @vchMName,
       @vchLName,
	   @vchFatherFName,
	   @vchFatherMName,
       @vchFatherLName,
       @vchAddress1,
       @vchAddress2,
       @vchCity,
       @vchState,
       @vchZipCode,
       @vchMobilePhone,
       @vchHomePhone,
       @vchOfficePhone,
       @vchDOB,
	   @vchSex 
   )
	SELECT @vchPID = @@Identity
   END
ELSE  IF EXISTS(SELECT     PID
   FROM Patient
   WHERE   (PID = @vchPID)
   )

   BEGIN
   UPDATE Patient
   SET
       FName = @vchFName,
	   MName = @vchMName,
       LName = @vchLName,
	   FatherFName = @vchFatherFName,
	   FatherMName = @vchFatherMName,
       FatherLName = @vchFatherLName,
       Address1 = @vchAddress1,
       Address2 = @vchAddress2,
       City = @vchCity,
       State = @vchState,
       ZipCode = @vchZipCode,
       MobilePhone = @vchMobilePhone,
       HomePhone = @vchHomePhone,
       OfficePhone = @vchOfficePhone,
       DOB = @vchDOB,
	   Sex = @vchSex 
   WHERE (PID = @vchPID)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg1 varchar(800)
       SET @SPErrMsg1 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in Patient table.'
       RAISERROR (@SPErrMsg1,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
	   SET @vchOutPID = @vchPID
   END

   SELECT @vchOutCRN = CRNumber FROM GlobalData
   IF @vchConsultant IS NOT NULL
       SELECT @biConsultantID = ID FROM Consultant WHERE [Name]=@vchConsultant
   IF @vchPackage IS NOT NULL
	   SELECT @biPackageID = ID FROM Package WHERE [Name]=@vchPackage

   BEGIN
   INSERT INTO OpenCases(
       CRNumber,
       PID,
	   Consultant,
       PatientType,
	   RegistrationFees,
	   ConsultationFees,
	   InitialDeposit,
	   Package,
	   RegistrationTime
  )
   VALUES (
       @vchOutCRN,
       @vchPID,
	   @biConsultantID,
       @vchPatientType,
	   @vchRegistrationFees,
	   @vchConsultationFees ,
	   @vchInitialDeposit,
	   @biPackageID,
	   getdate()
   )
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg2 varchar(800)
       SET @SPErrMsg2 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg2,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

IF (@vchConsultationFees = 0)
   BEGIN
      SELECT @bConsultationFeesPaid = 0
   END
ELSE
   BEGIN
	  SELECT @bConsultationFeesPaid = 1
   END


   BEGIN
   INSERT INTO PatientConsultantVisit(
       CRNumber,
       Consultant,
       Date,
       Paid
   )
   VALUES (
       @vchOutCRN,
       @biConsultantID,
       getdate(),
       @bConsultationFeesPaid
   )
   END
   
   IF (@@ERROR <> 0)
     BEGIN
	   ROLLBACK
       declare @SPErrMsg3 varchar(800)
       SET @SPErrMsg3 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in PatientConsultantVisit table.'
       RAISERROR (@SPErrMsg3,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
     END
   ELSE
     BEGIN
       Select @iRetValue = 0
     END
   

   BEGIN
   UPDATE GlobalData
   SET
      CRNumber = @vchOutCRN + 1
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg4 varchar(800)
       SET @SPErrMsg4 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg4,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

COMMIT
   
   RETURN @iRetValue

GO

GRANT EXEC ON dbo.SP_SetPatient TO hms_usr
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_UpdateOpenCase') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_UpdateOpenCase
GO


/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/11/2008
'SP Name    : dbo.SP_UpdateOpenCase
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Modified By       Date				   Remarks                             
'-----------       ------------        ----------------------------------
' Swati				10/21/2008		   Insert into PatientConsultantVisit                                                                         
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_UpdateOpenCase]

@biCRNumber    bigint,
@vchConsultant    Varchar(50),
@smRegFees smallmoney,
@mDeposit money,
@vchPackage Varchar(50),
@vchPatientType Varchar(1)
                    
AS
DECLARE  @iRetValue integer, 
	     @biConsultantID bigint,
		 @biPackageID bigint,
         @biOldConsultantID bigint
BEGIN TRANSACTION
   IF @vchConsultant IS NOT NULL
       SELECT @biConsultantID = ID FROM Consultant WHERE [Name]=@vchConsultant
   IF @vchPackage IS NOT NULL
	   SELECT @biPackageID = ID FROM Package WHERE [Name]=@vchPackage
   SELECT @biOldConsultantID = Consultant FROM OpenCases WHERE CRNumber=@biCRNumber

   BEGIN
   UPDATE OpenCases
   SET
       Consultant = @biConsultantID,
	   PatientType = @vchPatientType,
	   RegistrationFees = @smRegFees,
	   InitialDeposit = @mDeposit,
	   Package = @biPackageID
   WHERE (CRNumber = @biCRNumber)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg1 varchar(800)
       SET @SPErrMsg1 = 'Error: Error in Stored procedure dbo.SP_UpdateOpenCase for CRNumber : ' + CAST (@biCRNumber as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg1,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

IF (@biOldConsultantID != @biConsultantID)
   BEGIN
   INSERT INTO PatientConsultantVisit(
       CRNumber,
       Consultant,
       Date,
       Paid
   )
   VALUES (
       @biCRNumber,
       @biConsultantID,
       getdate(),
       0
   )
   
   IF (@@ERROR <> 0)
     BEGIN
	   ROLLBACK
       declare @SPErrMsg2 varchar(800)
       SET @SPErrMsg2 = 'Error: Error in Stored procedure dbo.SP_UpdateOpenCase for CRNumber : ' + CAST (@biCRNumber as varchar) + ' in PatientConsultantVisit table.'
       RAISERROR (@SPErrMsg2,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
     END
   ELSE
     BEGIN
       Select @iRetValue = 0
     END
   END

COMMIT
RETURN @iRetValue

GO

GRANT EXEC ON dbo.SP_UpdateOpenCase TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatientConsultantVisit') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatientConsultantVisit
GO



/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/25/2008
'SP Name    : dbo.SP_GetPatientConsultantVisit
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetPatientConsultantVisit]
@biCRN bigint
AS
   BEGIN
   SELECT
       PatientConsultantVisit.ID AS ID,
       Consultant.[Name] AS [ConsultantName],
       PatientConsultantVisit.Date AS Date,
	   PatientConsultantVisit.Paid AS Paid
   FROM PatientConsultantVisit INNER JOIN Consultant
   ON PatientConsultantVisit.Consultant = Consultant.ID
   WHERE PatientConsultantVisit.CRNumber=@biCRN
   END
GO

GRANT EXEC ON dbo.SP_GetPatientConsultantVisit TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetAllWards') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetAllWards
GO

/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 10/26/2008
'SP Name    : dbo.SP_GetAllWards
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetAllWards]
AS
   BEGIN
   SELECT
	   WardId,
	   WardName,
	   Location,
	   WardType,
	   NumberOfBeds,
	   Remarks
   FROM Ward
   END
GO

GRANT EXEC ON dbo.SP_GetAllWards TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatientWard') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatientWard
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 9/20/2008
'SP Name    : dbo.SP_SetPatientWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_SetPatientWard]
@biCRNumber    bigint,
@biWardId    bigint,
@biBedId    bigint,
@dtStartDate    Datetime
AS
DECLARE  @iRetValue     integer,
		 @errMsg		varchar(800),
         @biOldBedId    bigint
BEGIN TRANSACTION

   SELECT @biOldBedId = BedId FROM PatientWard WHERE (CRNumber = @biCRNumber AND EndDate IS NULL)

   BEGIN
   UPDATE PatientWard
   SET
	   EndDate = @dtStartDate
   WHERE (CRNumber = @biCRNumber AND EndDate IS NULL)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in setting end date in Stored procedure dbo.SP_SetPatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

   BEGIN
   UPDATE Bed
   SET
	   BedStatus = 'U'
   WHERE (BedId = @biOldBedId)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in marking bed status as U in Stored procedure dbo.SP_SetPatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

   BEGIN
   INSERT INTO PatientWard(
       CRNumber,
       WardId,
       BedId,
       StartDate
  )
   VALUES (
       @biCRNumber,
       @biWardId,
       @biBedId,
       @dtStartDate
   )
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in allocating new bed in Stored procedure dbo.SP_SetPatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

   BEGIN
   UPDATE Bed
   SET
	   BedStatus = 'O'
   WHERE (BedId = @biBedId)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in marking bed status as O in Stored procedure dbo.SP_SetPatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
COMMIT

RETURN @iRetValue

GO

GRANT EXEC ON dbo.SP_SetPatientWard TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatientWardHistory') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatientWardHistory
GO

/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 11/14/2008
'SP Name    : dbo.SP_GetPatientWardHistory
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetPatientWardHistory]
@biCRNumber bigint
AS
   BEGIN
   SELECT
       PatientWard.ID AS ID,
	   Ward.WardName AS WardName,
	   Bed.BedNumber AS BedNumber,
	   StartDate,
	   EndDate
   FROM (PatientWard INNER JOIN Ward
		 ON PatientWard.WardId = Ward.WardId) INNER JOIN Bed
   ON PatientWard.BedId = Bed.BedID
   WHERE CRNumber = @biCRNumber
   END

GO

GRANT EXEC ON dbo.SP_GetPatientWardHistory TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DischargePatientWard') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DischargePatientWard
GO

/*----------------------------------------------------------------------
'Created By : Swati
'Created On : 11/15/2008
'SP Name    : dbo.SP_DischargePatientWard
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_DischargePatientWard]
@biCRNumber    bigint,
@dtEndDate     Datetime
AS
DECLARE  @iRetValue     integer,
		 @errMsg		varchar(800),
		 @biOldBedId    bigint
BEGIN TRANSACTION

   SELECT @biOldBedId = BedId FROM PatientWard WHERE (CRNumber = @biCRNumber AND EndDate IS NULL)

   BEGIN
   UPDATE PatientWard
   SET
	   EndDate = @dtEndDate
   WHERE (CRNumber = @biCRNumber AND EndDate IS NULL)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in setting end date in Stored procedure dbo.SP_DischargePatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

   BEGIN
   UPDATE Bed
   SET
	   BedStatus = 'U'
   WHERE (BedId = @biOldBedId)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       SET @errMsg = 'Error: Error in marking bed status as U in Stored procedure dbo.SP_DischargePatientWard for CRNumber : ' + CAST (@biCRNumber as varchar)
       RAISERROR (@errMsg,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

COMMIT

RETURN @iRetValue

GO

GRANT EXEC ON dbo.SP_DischargePatientWard TO hms_usr
GO
